﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Reflection;
using ClosedXML.Excel;
using SuppKids.Core.Domain;
using SuppKids.Core.Extensions;
using SuppKids.Core.Reporting.Data;
using SuppKids.Core.Stuff;
using SuppKids.Resources;
using System.Globalization;

namespace SuppKids.Core.Reporting.Excel
{
	public class MenuRequirementReportGenerator : ExcelReportGenerator
	{
		private readonly StuffInfoStorage stuffInfoStorage;

		public MenuRequirementReportGenerator(StuffInfoStorage stuffInfoStorage)
		{
			this.stuffInfoStorage = stuffInfoStorage;
		}

		protected override void CreateWorkbook(out XLWorkbook workbook, out IXLWorksheet worksheet)
		{
			workbook = new XLWorkbook(Assembly.GetExecutingAssembly().GetManifestResourceStream("SuppKids.Core.Reporting.Excel.Templates.MenuRequirementReport.xlsx"));
			worksheet = workbook.Worksheets.FirstOrDefault() ?? workbook.Worksheets.Add(workbook.Worksheets.Count.ToString(this.FormatProvider));
		}

		protected override void CreateContent(IXLWorksheet worksheet, IReportData reportData)
		{
			var stuffInfo = stuffInfoStorage.Get();
			var data = reportData as MenuRequirementReportData;
            //CultureInfo elGR = CultureInfo.CreateSpecificCulture("el-GR");
			if (data == null)
			{
				throw new InvalidOperationException("Report data is not valid for this kind of report.");
			}
            // посчитаем сумму по группам
            double[] childreCountGroupsPrices = new double[data.GroupsData.Count()];
            for (int i = 0; i < childreCountGroupsPrices.Count(); i++)
                childreCountGroupsPrices[i] = 0;
            //int gdp = 0;
           
            double priceListTotal = 0;
            //worksheet.PageSetup.PrintAreas = 
            /*
            string allGroupsString = "";
            foreach (GroupData group in data.GroupsData)
                allGroupsString = allGroupsString + '/';
            allGroupsString.TrimEnd('/');
            */

			worksheet.Cell("A2").Value = string.Format("{0} - {1}", Labels.Label_ChildrenCount, data.GroupsData.Aggregate(string.Empty, (s, groupData) => s + groupData.Group.Name + "\\").TrimEnd('\\'));
			worksheet.Cell("B6").Value = data.GroupsData.Aggregate(string.Empty, (s, groupData) => s + /*groupData.ChildrenCount[] + */"\\").TrimEnd('\\');

			worksheet.Columns().Width = 17.4;
			worksheet.Column("A").Width = 40;
			worksheet.Row(9).Height = 60;

			worksheet.Cell("A1").Value = stuffInfo.CompanyName;
			worksheet.Cell("G4").Value = string.Format(Labels.Label_ReportForDateFormat, DateTime.Now.ToString("dd MMMM yyyy", this.FormatProvider));
			worksheet.Cell("K2").Value = stuffInfo.CompanyName;
			worksheet.Cell("K3").Value = string.Format("Заведующий ____________ {0}.", stuffInfo.Rukovoditel);
			worksheet.Cell("K4").Value = string.Format(Labels.Label_SignatureDateFormat, DateTime.Now.Year);

			var startOfRangeCell = worksheet.Cell("B8");

			var index = 12;

			var emptyConsumptionString = data.GroupsData.Aggregate(string.Empty, (current, @group) => current + ("0\\")).TrimEnd('\\');
			//var ingredientNameFormat = data.GroupsData.Aggregate("{0}, расход ", (current, @group) => current + string.Format("{0}\\", @group.Group.Name)).TrimEnd('\\');
			foreach (var product in data.AllProducts)
			{
                worksheet.Cell(string.Format("A{0}", index)).Value = product.Name;//string.Format(ingredientNameFormat, product.Name);//+ "- " + allGroupsString;
				index++;
			}

			var dailyMenus = data.DailyMenus.Where(a => a.Dishes.Any()).OrderBy(a => a.MealType).ToArray();
			//var dailyMenusCount = dailyMenus.Length;
			//var dishesCount = dailyMenus.Sum(a => a.Dishes.Count);
            int dailyMenusCount = 0, dishesCount = 0;
            foreach (var dailyMenu in dailyMenus)
            {
                uint totalMealChildrenCount = 0;
				foreach (var groupData in data.GroupsData)
                    totalMealChildrenCount += groupData.ChildrenCount[dailyMenu.MealType];
                if (totalMealChildrenCount == 0)
                    continue;
                dailyMenusCount++;
                foreach (var dish in dailyMenu.Dishes)
                    dishesCount++;
            }


			var range = worksheet.Range(string.Format("A8:{0}9", worksheet.Cell("A9").CellRight(dishesCount + dailyMenusCount + 5).Address.ColumnLetter));
			range.Style.Alignment.WrapText = true;
			range.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
			range.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
			range.Style.Border.OutsideBorder = XLBorderStyleValues.Medium;
			range.Style.Border.InsideBorder = XLBorderStyleValues.Medium;
			range.Style.Font.FontSize = 12;
			range.Style.Font.Bold = true;




			foreach (var dailyMenu in dailyMenus)
			{
				startOfRangeCell.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
				startOfRangeCell.Style.Fill.SetBackgroundColor(XLColor.FromArgb(242, 220, 219));

				var mealGroups = string.Empty;
				var mealChildrenCount = string.Empty;

                uint totalMealChildrenCount = 0;
				foreach (var groupData in data.GroupsData)
				{
					mealGroups = mealGroups + groupData.Group.Name + "/";
					mealChildrenCount = mealChildrenCount + groupData.ChildrenCount[dailyMenu.MealType] + "/";
                    totalMealChildrenCount += groupData.ChildrenCount[dailyMenu.MealType];
				}

                if (totalMealChildrenCount == 0)
                    continue;

				startOfRangeCell.Value = string.Format("{0} ({1} - {2})", dailyMenu.MealType.GetLocalizedEnumValue(), mealGroups.TrimEnd('/'), mealChildrenCount.TrimEnd('/'));


				var endOfRangeCell = startOfRangeCell.CellRight(dailyMenu.Dishes.Count > 0 ? dailyMenu.Dishes.Count : 0);
				worksheet.Range(startOfRangeCell, endOfRangeCell).Merge();

				var dishNameCell = startOfRangeCell.CellBelow(1);
				var dishWeightCell = dishNameCell.CellBelow(2);


				foreach (var dish in dailyMenu.Dishes)
				{
					index = 12;
					dishNameCell.Value = dish.Name;
					dishNameCell.Style.Fill.SetBackgroundColor(XLColor.FromArgb(166, 166, 166));
					dishWeightCell.Value = string.Format("{0}г.", data.GroupsData.Select(groupData => dish.DishAmounts.FirstOrDefault(a => a.Group.Id == groupData.Group.Id && a.DailyMenu.Id == dailyMenu.Id) ?? new DishAmount(groupData.Group, dailyMenu)).Aggregate("", (current, amount) => current + (amount.Amount + "\\")).TrimEnd('\\'));

					var letter = dishNameCell.Address.ColumnLetter;

					foreach (var product in data.AllProducts)
					{
						var disIngredient = dish.Ingredients.FirstOrDefault(a => a.Product.Id == product.Id);

                        var value = "";// emptyConsumptionString;
						if (disIngredient != null)
						{
							var result = string.Empty;
							foreach (var groupData in data.GroupsData)
							{
								result = result + (disIngredient.ConsumptionValues.FirstOrDefault(a => a.Group.Id == groupData.Group.Id && a.DailyMenu.Id == dailyMenu.Id).Return(a => a.Value, 0) + "\\");
							}

							value = result.TrimEnd('\\');
						}

						worksheet.Cell(string.Format("{0}{1}", letter, index)).Value = value;

						index++;
					}

					dishNameCell = dishNameCell.CellRight(1);
					dishWeightCell = dishWeightCell.CellRight(1);
				}

                dishNameCell.Value = Labels.TableHeader_SpentTotalFor + dailyMenu.MealType.GetLocalizedEnumValue().ToLower();
				dishNameCell.Style.Font.Bold = false;
				dishNameCell.Style.Fill.SetBackgroundColor(XLColor.FromArgb(242, 242, 242));

				index = 12;
				foreach (var product in data.AllProducts)
				{
					var ingredients = dailyMenu.Dishes.Select(a => a.Ingredients.FirstOrDefault(b => b.Product.Id == product.Id)).Where(a=> a != null).ToArray();

					var spent = emptyConsumptionString;
					if (ingredients.Any())
					{
						var result = string.Empty;
                        //int g = 0;
						foreach (var groupData in data.GroupsData)
						{
							var summ =
								ingredients.Sum(
									i =>
										i.ConsumptionValues.FirstOrDefault(a => a.Group.Id == groupData.Group.Id && a.DailyMenu.Id == dailyMenu.Id)
											.Return(a => a.Value, 0))*groupData.ChildrenCount[dailyMenu.MealType];

							result += (summ > 0 ? summ.ToString("F1") : "0") + "\\";

                            //??
                            //childreCountGroupsPrices[g] += (uint)summ;
                            //g++;
						}

						spent = result.TrimEnd('\\');
					}

					worksheet.Cell(string.Format("{0}{1}", dishNameCell.Address.ColumnLetter, index)).Value = spent;

					index++;
				}
				
				startOfRangeCell = endOfRangeCell.CellRight(1);
			}

			var cipherCell = startOfRangeCell.CellBelow(2);
			var spentCell = cipherCell.CellRight(1);
			var spentTotalCell = cipherCell.CellRight(2);
			var priceForOneCell = cipherCell.CellRight(3);
			var priceTotalCell = cipherCell.CellRight(4);

			cipherCell.Value = Labels.TableHeader_Cipher;
			spentCell.Value = Labels.TableHeader_Spent;
			spentTotalCell.Value = Labels.TableHeader_SpentTotal;
			priceForOneCell.Value = Labels.TableHeader_PriceForOne;
			priceTotalCell.Value = Labels.TableHeader_PriceTotal;

			var cipherLetter = cipherCell.Address.ColumnLetter;
			var spentLetter = spentCell.Address.ColumnLetter;
			var spentTotalLetter = spentTotalCell.Address.ColumnLetter;
			var priceForOneLetter = priceForOneCell.Address.ColumnLetter;
			var priceTotalLetter = priceTotalCell.Address.ColumnLetter;

			index = 12;
            int totalPrice = 0;
			foreach (var product in data.AllProducts)
			{
				var spent = new Dictionary<int, double>();
				var price = new Dictionary<int, double>();
				foreach (var groupData in data.GroupsData)
				{
					spent[groupData.Group.Id] = 0;
					price[groupData.Group.Id] = 0;
					foreach (var dailyMenu in dailyMenus)
					{
						var ingredients = dailyMenu.Dishes.Select(a => a.Ingredients.FirstOrDefault(b => b.Product.Id == product.Id)).Where(a => a != null).ToArray();

						var sum = ingredients
							.Sum(i => i.ConsumptionValues
								.FirstOrDefault(a =>
									a.Group.Id == groupData.Group.Id &&
									a.DailyMenu.Id == dailyMenu.Id)
								.Return(a => a.Value*groupData.ChildrenCount[a.DailyMenu.MealType], 0));
						spent[groupData.Group.Id] += sum;
						price[groupData.Group.Id] += sum*product.Price;
					}
				}

				var spentTotal = spent.Sum(a => a.Value);
				var priceTotal = price.Sum(a => a.Value);

                string priceTotalInGroups = "";
                int gdp = 0;
                foreach (var p in price)
                {
                    priceTotalInGroups += ( Math.Ceiling(p.Value) / 1000 ).ToString() + '\\';
                    childreCountGroupsPrices[gdp] += p.Value;
                    gdp++;
                }
                priceTotalInGroups.Trim('\\');

				var spentString = spent.Aggregate(string.Empty, (a, b) => a + "\\" + (b.Value > 0 ? b.Value.ToString("F1") : "0")).Trim('\\');

				if (spentString == emptyConsumptionString)
				{
					worksheet.Row(index).Delete();
				}
				else
				{
					worksheet.Cell(string.Format("{0}{1}", cipherLetter, index)).Value = product.Cipher;
					worksheet.Cell(string.Format("{0}{1}", spentLetter, index)).Value = spentString;
					var spentTotalValueCell = worksheet.Cell(string.Format("{0}{1}", spentTotalLetter, index)); // надо округлить
                  
                    //totalPrice += (int)(spentTotal) + 1;
                    totalPrice += (int)Math.Ceiling(spentTotal);
                    spentTotalValueCell.Value = Math.Ceiling(spentTotal);

					spentTotalValueCell.Style.NumberFormat.Format = @"0";
                    //spentTotalValueCell.Style.NumberFormat.Format = @"0.0";

					worksheet.Cell(string.Format("{0}{1}", priceForOneLetter, index)).Value = product.Price;

					var priceTotalValueCell = worksheet.Cell(string.Format("{0}{1}", priceTotalLetter, index));

                    //priceTotalValueCell.Value = Math.Ceiling(priceTotal).ToString("0,0", elGR);
                    priceTotalValueCell.Value = priceTotalInGroups; 
                    priceListTotal += priceTotal;
					//priceTotalValueCell.Style.NumberFormat.Format = @"#,##0.00р\.";
					index++;
				}
			}

            // удаляем ячейки 0\0

            worksheet.Cell(spentTotalLetter + (index).ToString()).Value = "Стоимость всех потраченых продуктов: " + ( Math.Ceiling(priceListTotal) / 1000 ).ToString();
            worksheet.Cell(spentTotalLetter + (index).ToString()).Style.Font.FontSize = 15;
            worksheet.Cell(spentTotalLetter + (index).ToString()).Style.Font.Bold = true;
            //totalPrice;

			range = worksheet.Range(string.Format("A10:{0}{1}", priceTotalLetter, index - 1));
			range.Style.Border.InsideBorder = XLBorderStyleValues.Thin;
			range.Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
			var rows = worksheet.Range(string.Format("A12:{0}{1}", priceTotalLetter, index - 1)).Rows().ToArray();
			for (var i = 0; i < rows.Length; i++)
			{
				var row = rows[i];
				var isEven = (i & 1) == 0;
				if (isEven)
				{
					row.Style.Fill.SetBackgroundColor(XLColor.FromArgb(217, 217, 217));
				}

				var shift = 1;
				foreach (var dailyMenu in dailyMenus)
				{
					shift += dailyMenu.Dishes.Count + 1;
					row.Cell(shift).Style.Fill.SetBackgroundColor(isEven ? XLColor.FromArgb(166, 166, 166) : XLColor.FromArgb(242, 242, 242));
				}
			}
            
			worksheet.Cell(string.Format("A{0}", index + 2)).Value = string.Format(Labels.Label_ChiefSignature, stuffInfo.Rukovoditel);
            worksheet.Cell(string.Format("B{0}", index + 2)).Value = Labels.Label_Signature;
            worksheet.Cell(string.Format("C{0}", index + 3)).Value = Labels.Label_SignatureName;


			worksheet.Cell(string.Format("A{0}", index + 4)).Value = string.Format(Labels.Label_CookSignature, stuffInfo.Povar);
            worksheet.Cell(string.Format("B{0}", index + 4)).Value = Labels.Label_Signature;
            worksheet.Cell(string.Format("C{0}", index + 5)).Value = Labels.Label_SignatureName;

			worksheet.Cell(string.Format("A{0}", index + 6)).Value = string.Format(Labels.Label_WarehouseKeeperSignature, stuffInfo.Kladovschik);
            worksheet.Cell(string.Format("B{0}", index + 6)).Value = Labels.Label_Signature;
            worksheet.Cell(string.Format("C{0}", index + 7)).Value = Labels.Label_SignatureName;

			worksheet.Cell(string.Format("A{0}", index + 8)).Value = string.Format(Labels.Label_DietSestraSignature, stuffInfo.DietSestra);
            worksheet.Cell(string.Format("B{0}", index + 8)).Value = Labels.Label_Signature;
            worksheet.Cell(string.Format("C{0}", index + 9)).Value = Labels.Label_SignatureName;
            
			worksheet.Style.Font.FontName = "Arial";
			worksheet.Style.Font.FontSize = 12;

            int lasrRow = index + 9;
            // добавляем по группам сверху
            uint[] childreCountGroups = new uint[data.GroupsData.Count()];
            for( int i = 0; i < childreCountGroups.Count(); i++ )
                childreCountGroups[i] = 0;
            uint[] childreCountGroups2 = new uint[data.GroupsData.Count()];
            for (int i = 0; i < childreCountGroups.Count(); i++)
                childreCountGroups2[i] = 0;

            // количество питающихся
            foreach (var dailyMenu in dailyMenus)
            {
                ///childreCountGroups[dailyMenu]
                int g = 0; 
                foreach (var groupData in data.GroupsData)
                {
                    childreCountGroups[g] += groupData.ChildrenCount[dailyMenu.MealType];
                    if (dailyMenu.MealType.Equals(MealType.Lunch))
                        childreCountGroups2[g] = groupData.ChildrenCount[dailyMenu.MealType];
                    g++;
                }           
            }
            /*

            
            foreach (var groupData in data.GroupsData)
            {
                foreach (var dailyMenu in dailyMenus)
                {
                    foreach (var product in data.AllProducts)
                    {
                        childreCountGroupsPrices[gdp] += groupData.ChildrenCount[dailyMenu.MealType] * product.Price;
                    }
                }

                gdp++;
            } 
            */
            worksheet.Cell("A2").Value = worksheet.Cell("A2").Value + " -";
            lasrRow += 2;
            //int startCol = 7, pasteRow = 8,
            int pasteCounter = -1, j = 0;
            foreach (GroupData group in data.GroupsData)
            {
                worksheet.Cell("A2").Value = worksheet.Cell("A2").Value + childreCountGroups2[j].ToString() + "/";
                worksheet.Range("A6:E7").CopyTo(worksheet.Cell("A" + (lasrRow + 4 + 3 * pasteCounter).ToString()));
                worksheet.Cell("A" + (lasrRow + 5 + 3 * pasteCounter).ToString()).Value = group.Group.Name;
                worksheet.Cell("B" + (lasrRow + 5 + 3 * pasteCounter - 1).ToString()).Value = "Плановая стоимость дня";
                worksheet.Cell("C" + (lasrRow + 5 + 3 * pasteCounter).ToString()).Value = childreCountGroups2[j].ToString();
                worksheet.Cell("D" + (lasrRow + 5 + 3 * pasteCounter).ToString()).Value = Math.Ceiling(childreCountGroupsPrices[j]).ToString();
                if (childreCountGroups2[j] != 0)
                    worksheet.Cell("E" + (lasrRow + 5 + 3 * pasteCounter).ToString()).Value =
                        Math.Ceiling(childreCountGroupsPrices[j]) / childreCountGroups2[j] / 1000;
                else
                    worksheet.Cell("E" + (lasrRow + 5 + 3 * pasteCounter).ToString()).Value = 0;
                j++;
                pasteCounter++;
            }
            
            worksheet.Row(6).Delete();
            worksheet.Row(6).Delete();

            worksheet.Cell("A2").Value = worksheet.Cell("A2").Value.ToString().TrimEnd('/');

            
            worksheet.Row(5).InsertRowsAbove(8);
            //worksheet.Row(4).InsertRowsBelow(4);
            worksheet.Range("A" + ( index + 8).ToString() +":D" + ( index + 16 ).ToString() ).CopyTo(worksheet.Cell("A4"));
            for(int i = 0; i < 8 ; i++)
               worksheet.Row(index + 8 ).Delete();
            
            //worksheet.PageSetup.PrintAreas.Add("A1:A" + (index + 9 + 3 * pasteCounter).ToString());
            //worksheet.PageSetup.sc
		}
	}
}